package com.b511.swpu.cementbackend.repository;

import com.b511.swpu.cementbackend.dto.output.WellinfoDto;
import com.b511.swpu.cementbackend.entity.CM_WorkBaseInfo;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.List;

public interface CM_WorkBaseInfoRepository extends JpaRepository<CM_WorkBaseInfo, String>, JpaSpecificationExecutor<CM_WorkBaseInfo> {

    @Query(value = "select distinct country from CM_WORK_BASE_INFO where workcompany=:workcompany",nativeQuery = true)
    List<String> findCountry(@Param("workcompany") String workcompany);

    @Query(value = "select distinct area from CM_WORK_BASE_INFO where workcompany=:workcompany and country=:country",nativeQuery = true)
    List<String> findArea(@Param("workcompany") String workcompany,@Param("country") String country);

    @Query(value = "select distinct block from CM_WORK_BASE_INFO where workcompany=:workcompany and country=:country and area=:area",nativeQuery = true)
    List<String> findBlock(@Param("workcompany") String workcompany,@Param("country") String country,@Param("area") String area);

    @Query(value = "select distinct oilfield from CM_WORK_BASE_INFO where workcompany=:workcompany and country=:country and area=:area and block=:block",nativeQuery = true)
    List<String> findOilField(@Param("workcompany") String workcompany,@Param("country") String country,@Param("area") String area,@Param("block") String block);

    @Query(value = "select distinct wellname from CM_WORK_BASE_INFO where workcompany=:workcompany and country=:country and area=:area and block=:block and oilfield=:oilfield",nativeQuery = true)
    List<String> findWellName(@Param("workcompany") String workcompany,@Param("country") String country,@Param("area") String area,@Param("block") String block,@Param("oilfield") String oilfield);

    @Query(value = "select distinct casingsize from CM_WORK_BASE_INFO where workcompany=:workcompany and country=:country and area=:area and block=:block and oilfield=:oilfield and wellname=:wellname",nativeQuery = true)
    List<String> findCasingSize(@Param("workcompany") String workcompany,@Param("country") String country,@Param("area") String area,@Param("block") String block,@Param("oilfield") String oilfield,@Param("wellname") String wellname);

    //由于这里使用的是HQL，所以相应的字段应与实体类对应。
    @Query(value = "select new com.b511.swpu.cementbackend.dto.output.WellinfoDto(CM_WorkBaseInfo_ID,casingSize) FROM CM_WorkBaseInfo where workcompany=:workcompany and country=:country and area=:area and block=:block and oilfield=:oilfield and wellname=:wellname and casingSize=:casingSize")
    WellinfoDto findit(@Param("workcompany") String workcompany,@Param("country") String country,@Param("area") String area,@Param("block") String block,@Param("oilfield") String oilfield,@Param("wellname") String wellname, @Param("casingSize") String casingSize);

    @Query(value = "select * from CM_WORK_BASE_INFO where CM_WORK_BASE_INFO_ID=:cm_WorkBaseInfo_ID",nativeQuery = true)
    CM_WorkBaseInfo findwellInfoDetailById(@Param("cm_WorkBaseInfo_ID") String cm_WorkBaseInfo_ID);

    List<CM_WorkBaseInfo> findByWellName(String wellName);

    @Query(value = "select new com.b511.swpu.cementbackend.dto.output.WellinfoDto(CM_WorkBaseInfo_ID,casingSize) FROM CM_WorkBaseInfo where workcompany=:workcompany and country=:country and area=:area and block=:block and oilfield=:oilfield and wellname=:wellname")
    List<WellinfoDto> findCasingsizeAndID(@Param("workcompany") String workcompany,@Param("country") String country,@Param("area") String area,@Param("block") String block,@Param("oilfield") String oilfield,@Param("wellname") String wellname);

    @Query(value = "select new com.b511.swpu.cementbackend.dto.output.WellinfoDto(CM_WorkBaseInfo_ID,casingSize) FROM CM_WorkBaseInfo where country=:country and area=:area and block=:block and oilfield=:oilfield and wellname=:wellname")
    List<WellinfoDto> findSizeAndId(@Param("country") String country,@Param("area") String area,@Param("block") String block,@Param("oilfield") String oilfield,@Param("wellname") String wellname);

    // 11.16 casingSize 去重
    @Query(value = "select distinct new com.b511.swpu.cementbackend.dto.output.WellinfoDto(CM_WorkBaseInfo_ID,casingSize) FROM CM_WorkBaseInfo where wellname=:wellname and casingSize=:casingSize")
    List<WellinfoDto> findSizeAndIdDistinct(@Param("wellname") String wellname, @Param("casingSize") String casingSize);


}
